// V-backend/src/controllers/fansController.js
const db = require('../models');
const { Op } = require('sequelize');

const fansController = {
  // 获取粉丝列表
  getFansList: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      const { 
        page = 1, 
        limit = 10, 
        search, 
        activity_level,
        sort_by = 'recent',
        start_date,
        end_date
      } = req.query;
      
      console.log('👥 获取粉丝列表:', { 
        anchorId, 
        role: userRole, 
        page, 
        limit, 
        search,
        activity_level,
        sort_by
      });

      // 构建基础查询
      let baseQuery = `
        SELECT 
          a.audience_id,
          a.audience_name,
          a.avatar,
          a.gender,
          a.email,
          a.created_at as register_time,
          COUNT(DISTINCT r.registration_id) as activity_count,
          COUNT(DISTINCT o.order_id) as order_count,
          COALESCE(SUM(oi.subtotal), 0) as total_spent,
          MAX(r.registration_time) as last_activity_time
        FROM v_audience a
        LEFT JOIN v_registration r ON a.audience_id = r.audience_id
        LEFT JOIN v_activity act ON r.activity_id = act.activity_id
        LEFT JOIN v_order o ON a.audience_id = o.audience_id
        LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
        LEFT JOIN v_product p ON oi.product_id = p.product_id
      `;

      // 构建WHERE条件
      let whereConditions = [];
      let replacements = [];

      // 权限限制：普通主播只能看到参与自己活动或购买自己商品的粉丝
      if (userRole !== 'super_admin') {
        whereConditions.push(`(act.anchor_id = ? OR p.anchor_id = ?)`);
        replacements.push(anchorId, anchorId);
      }

      // 搜索条件
      if (search && search.trim() !== '') {
        whereConditions.push(`(a.audience_name LIKE ? OR a.email LIKE ?)`);
        replacements.push(`%${search}%`, `%${search}%`);
      }

      // 日期筛选
      if (start_date && end_date) {
        whereConditions.push(`a.created_at BETWEEN ? AND ?`);
        replacements.push(new Date(start_date), new Date(end_date + ' 23:59:59'));
      }

      // 添加WHERE子句
      if (whereConditions.length > 0) {
        baseQuery += ` WHERE ${whereConditions.join(' AND ')}`;
      }

      // 添加GROUP BY
      baseQuery += ` GROUP BY a.audience_id, a.audience_name, a.avatar, a.gender, a.email, a.created_at`;

      // 活跃度筛选
      let havingConditions = [];
      if (activity_level) {
        switch (activity_level) {
          case 'high':
            havingConditions.push(`activity_count >= 5`);
            break;
          case 'medium':
            havingConditions.push(`activity_count BETWEEN 2 AND 4`);
            break;
          case 'low':
            havingConditions.push(`activity_count <= 1`);
            break;
          case 'buyer':
            havingConditions.push(`order_count > 0`);
            break;
          case 'non_buyer':
            havingConditions.push(`order_count = 0`);
            break;
        }
      }

      if (havingConditions.length > 0) {
        baseQuery += ` HAVING ${havingConditions.join(' AND ')}`;
      }

      // 修复排序：移除 NULLS LAST，MySQL 默认 NULL 值在最后
      switch (sort_by) {
        case 'recent':
          baseQuery += ` ORDER BY last_activity_time DESC`;
          break;
        case 'active':
          baseQuery += ` ORDER BY activity_count DESC`;
          break;
        case 'spent':
          baseQuery += ` ORDER BY total_spent DESC`;
          break;
        case 'register':
          baseQuery += ` ORDER BY register_time DESC`;
          break;
        default:
          baseQuery += ` ORDER BY last_activity_time DESC`;
      }

      // 分页
      const offset = (page - 1) * limit;
      baseQuery += ` LIMIT ? OFFSET ?`;
      replacements.push(parseInt(limit), offset);

      console.log('📋 粉丝查询SQL:', baseQuery);
      console.log('🔧 查询参数:', replacements);

      // 执行查询
      const fans = await db.sequelize.query(baseQuery, {
        replacements,
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 获取总数
      let countQuery = `
        SELECT COUNT(DISTINCT a.audience_id) as total
        FROM v_audience a
        LEFT JOIN v_registration r ON a.audience_id = r.audience_id
        LEFT JOIN v_activity act ON r.activity_id = act.activity_id
        LEFT JOIN v_order o ON a.audience_id = o.audience_id
        LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
        LEFT JOIN v_product p ON oi.product_id = p.product_id
      `;

      if (whereConditions.length > 0) {
        countQuery += ` WHERE ${whereConditions.join(' AND ')}`;
      }

      // 总数查询的HAVING条件
      if (havingConditions.length > 0) {
        countQuery = `
          SELECT COUNT(*) as total FROM (${countQuery} GROUP BY a.audience_id HAVING ${havingConditions.join(' AND ')}) as filtered_fans
        `;
      }

      const totalResult = await db.sequelize.query(countQuery, {
        replacements: replacements.slice(0, -2), // 移除分页参数
        type: db.Sequelize.QueryTypes.SELECT
      });

      const total = parseInt(totalResult[0]?.total || 0);

      // 计算活跃度等级
      const fansWithLevel = fans.map(fan => {
        let activityLevel = 'low';
        if (fan.activity_count >= 5) {
          activityLevel = 'high';
        } else if (fan.activity_count >= 2) {
          activityLevel = 'medium';
        }

        return {
          ...fan,
          activity_level: activityLevel,
          is_buyer: fan.order_count > 0
        };
      });

      console.log('✅ 粉丝列表获取成功:', { count: fans.length, total });

      res.json({
        success: true,
        data: {
          fans: fansWithLevel,
          pagination: {
            total,
            current: parseInt(page),
            pageSize: parseInt(limit),
            totalPages: Math.ceil(total / limit)
          }
        }
      });

    } catch (error) {
      console.error('❌ 获取粉丝列表失败:', error);
      res.status(500).json({
        success: false,
        message: '获取粉丝列表失败: ' + error.message
      });
    }
  },

  // 获取粉丝统计数据
  getFansStats: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      
      console.log('📊 获取粉丝统计数据:', { anchorId, role: userRole });

      // 总粉丝数
      let totalFansQuery = `
        SELECT COUNT(DISTINCT a.audience_id) as total_fans
        FROM v_audience a
        LEFT JOIN v_registration r ON a.audience_id = r.audience_id
        LEFT JOIN v_activity act ON r.activity_id = act.activity_id
        LEFT JOIN v_order o ON a.audience_id = o.audience_id
        LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
        LEFT JOIN v_product p ON oi.product_id = p.product_id
        ${userRole !== 'super_admin' ? 'WHERE (act.anchor_id = ? OR p.anchor_id = ?)' : ''}
      `;

      const totalFansResult = await db.sequelize.query(totalFansQuery, {
        replacements: userRole !== 'super_admin' ? [anchorId, anchorId] : [],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 活跃度分布
      let activityLevelQuery = `
        SELECT 
          CASE 
            WHEN activity_count >= 5 THEN '高活跃'
            WHEN activity_count >= 2 THEN '中活跃' 
            ELSE '低活跃'
          END as level,
          COUNT(*) as fan_count
        FROM (
          SELECT 
            a.audience_id,
            COUNT(DISTINCT r.registration_id) as activity_count
          FROM v_audience a
          LEFT JOIN v_registration r ON a.audience_id = r.audience_id
          LEFT JOIN v_activity act ON r.activity_id = act.activity_id
          LEFT JOIN v_order o ON a.audience_id = o.audience_id
          LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
          LEFT JOIN v_product p ON oi.product_id = p.product_id
          ${userRole !== 'super_admin' ? 'WHERE (act.anchor_id = ? OR p.anchor_id = ?)' : ''}
          GROUP BY a.audience_id
        ) as fan_activity
        GROUP BY level
      `;

      const activityLevelResult = await db.sequelize.query(activityLevelQuery, {
        replacements: userRole !== 'super_admin' ? [anchorId, anchorId] : [],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 购买行为分布
      let buyerStatsQuery = `
        SELECT 
          CASE 
            WHEN order_count > 0 THEN '有购买'
            ELSE '无购买'
          END as buyer_type,
          COUNT(*) as fan_count
        FROM (
          SELECT 
            a.audience_id,
            COUNT(DISTINCT o.order_id) as order_count
          FROM v_audience a
          LEFT JOIN v_order o ON a.audience_id = o.audience_id
          LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
          LEFT JOIN v_product p ON oi.product_id = p.product_id
          ${userRole !== 'super_admin' ? 'WHERE p.anchor_id = ?' : ''}
          GROUP BY a.audience_id
        ) as fan_orders
        GROUP BY buyer_type
      `;

      const buyerStatsResult = await db.sequelize.query(buyerStatsQuery, {
        replacements: userRole !== 'super_admin' ? [anchorId] : [],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const statsData = {
        totalFans: parseInt(totalFansResult[0]?.total_fans || 0),
        activityLevel: activityLevelResult,
        buyerStats: buyerStatsResult
      };

      console.log('✅ 粉丝统计数据获取成功');

      res.json({
        success: true,
        data: statsData
      });

    } catch (error) {
      console.error('❌ 获取粉丝统计数据失败:', error);
      res.status(500).json({
        success: false,
        message: '获取粉丝统计数据失败: ' + error.message
      });
    }
  },

  // 获取粉丝详情 - 修复路由冲突问题
  getFanDetail: async (req, res) => {
    try {
      const { id } = req.params;
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      
      console.log('🔍 获取粉丝详情:', { fanId: id, anchorId, role: userRole });

      // 检查是否是特殊路由（stats, export等）
      if (id === 'stats' || id === 'export' || id === 'list') {
        return res.status(404).json({
          success: false,
          message: '粉丝不存在'
        });
      }

      // 获取粉丝基础信息
      const fan = await db.Audience.findByPk(id, {
        attributes: { exclude: ['password'] }
      });

      if (!fan) {
        return res.status(404).json({
          success: false,
          message: '粉丝不存在'
        });
      }

      // 获取粉丝参与的活动
      let activityQuery = `
        SELECT 
          a.activity_id,
          a.title,
          a.start_time,
          a.status,
          r.registration_time,
          r.status as registration_status
        FROM v_registration r
        JOIN v_activity a ON r.activity_id = a.activity_id
        WHERE r.audience_id = ?
        ${userRole !== 'super_admin' ? 'AND a.anchor_id = ?' : ''}
        ORDER BY r.registration_time DESC
        LIMIT 10
      `;

      const activities = await db.sequelize.query(activityQuery, {
        replacements: userRole !== 'super_admin' ? [id, anchorId] : [id],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 获取粉丝订单记录
      let orderQuery = `
        SELECT 
          o.order_id,
          o.order_number,
          o.total_amount,
          o.status,
          o.created_at,
          COUNT(oi.order_item_id) as item_count
        FROM v_order o
        JOIN v_order_item oi ON o.order_id = oi.order_id
        JOIN v_product p ON oi.product_id = p.product_id
        WHERE o.audience_id = ?
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
        GROUP BY o.order_id, o.order_number, o.total_amount, o.status, o.created_at
        ORDER BY o.created_at DESC
        LIMIT 10
      `;

      const orders = await db.sequelize.query(orderQuery, {
        replacements: userRole !== 'super_admin' ? [id, anchorId] : [id],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 统计粉丝数据
      let statsQuery = `
        SELECT 
          COUNT(DISTINCT r.registration_id) as total_activities,
          COUNT(DISTINCT o.order_id) as total_orders,
          COALESCE(SUM(oi.subtotal), 0) as total_spent,
          MAX(r.registration_time) as last_activity_time
        FROM v_audience a
        LEFT JOIN v_registration r ON a.audience_id = r.audience_id
        LEFT JOIN v_activity act ON r.activity_id = act.activity_id
        LEFT JOIN v_order o ON a.audience_id = o.audience_id
        LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
        LEFT JOIN v_product p ON oi.product_id = p.product_id
        WHERE a.audience_id = ?
        ${userRole !== 'super_admin' ? 'AND (act.anchor_id = ? OR p.anchor_id = ?)' : ''}
      `;

      const statsResult = await db.sequelize.query(statsQuery, {
        replacements: userRole !== 'super_admin' ? [id, anchorId, anchorId] : [id],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const stats = statsResult[0] || {
        total_activities: 0,
        total_orders: 0,
        total_spent: 0,
        last_activity_time: null
      };

      // 计算活跃度等级
      let activityLevel = 'low';
      if (stats.total_activities >= 5) {
        activityLevel = 'high';
      } else if (stats.total_activities >= 2) {
        activityLevel = 'medium';
      }

      const fanDetail = {
        ...fan.toJSON(),
        stats: {
          ...stats,
          activity_level: activityLevel,
          is_buyer: stats.total_orders > 0
        },
        recent_activities: activities,
        recent_orders: orders
      };

      console.log('✅ 粉丝详情获取成功');

      res.json({
        success: true,
        data: fanDetail
      });

    } catch (error) {
      console.error('❌ 获取粉丝详情失败:', error);
      res.status(500).json({
        success: false,
        message: '获取粉丝详情失败: ' + error.message
      });
    }
  },

  // 导出粉丝数据
  exportFansData: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      
      console.log('📤 导出粉丝数据:', { anchorId, role: userRole });

      let exportQuery = `
        SELECT 
          a.audience_id as "用户ID",
          a.audience_name as "用户名",
          a.email as "邮箱",
          a.gender as "性别",
          a.created_at as "注册时间",
          COUNT(DISTINCT r.registration_id) as "参与活动数",
          COUNT(DISTINCT o.order_id) as "订单数",
          COALESCE(SUM(oi.subtotal), 0) as "总消费金额",
          MAX(r.registration_time) as "最后活动时间",
          CASE 
            WHEN COUNT(DISTINCT r.registration_id) >= 5 THEN '高活跃'
            WHEN COUNT(DISTINCT r.registration_id) >= 2 THEN '中活跃'
            ELSE '低活跃'
          END as "活跃等级",
          CASE 
            WHEN COUNT(DISTINCT o.order_id) > 0 THEN '是'
            ELSE '否'
          END as "是否购买"
        FROM v_audience a
        LEFT JOIN v_registration r ON a.audience_id = r.audience_id
        LEFT JOIN v_activity act ON r.activity_id = act.activity_id
        LEFT JOIN v_order o ON a.audience_id = o.audience_id
        LEFT JOIN v_order_item oi ON o.order_id = oi.order_id
        LEFT JOIN v_product p ON oi.product_id = p.product_id
        ${userRole !== 'super_admin' ? 'WHERE (act.anchor_id = ? OR p.anchor_id = ?)' : ''}
        GROUP BY a.audience_id, a.audience_name, a.email, a.gender, a.created_at
        ORDER BY "最后活动时间" DESC
      `;

      const fansData = await db.sequelize.query(exportQuery, {
        replacements: userRole !== 'super_admin' ? [anchorId, anchorId] : [],
        type: db.Sequelize.QueryTypes.SELECT
      });

      console.log('✅ 粉丝数据导出成功:', fansData.length, '条记录');

      res.json({
        success: true,
        data: fansData,
        message: `成功导出 ${fansData.length} 条粉丝数据`
      });

    } catch (error) {
      console.error('❌ 导出粉丝数据失败:', error);
      res.status(500).json({
        success: false,
        message: '导出粉丝数据失败: ' + error.message
      });
    }
  }
};

module.exports = fansController;